import pymysql
from pymysql import Error


def get_db_connection(config):
    """建立数据库连接"""
    try:
        connection = pymysql.connect(
            host=config['host'],
            user=config['user'],
            password=config['password'],
            database=config['database'],
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        return connection
    except Error as e:
        print(f"数据库连接失败: {e}")
        return None


def get_all_tables(connection):
    """获取数据库所有表名"""
    try:
        with connection.cursor() as cursor:
            cursor.execute("SHOW TABLES")
            tables = [row['Tables_in_' + connection.db.decode()] for row in cursor.fetchall()]
            return tables
    except Error as e:
        print(f"获取表名失败: {e}")
        return []


def get_column_details(connection, db_name, table_name):
    """获取表的列详细信息（包含注释）"""
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, 
                       COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT 
                FROM information_schema.columns 
                WHERE table_schema = %s AND table_name = %s
                ORDER BY ORDINAL_POSITION
            """, (db_name, table_name))
            return cursor.fetchall()
    except Error as e:
        print(f"获取表 {table_name} 列信息失败: {e}")
        return []


def generate_alter_statements(column_details):
    """生成ALTER TABLE语句（包含注释处理）"""
    alter_parts = []
    new_names = [col['COLUMN_NAME'].lower() for col in column_details]

    # 检查新列名是否有重复
    if len(new_names) != len(set(new_names)):
        return None

    for col in column_details:
        original_name = col['COLUMN_NAME']
        new_name = original_name.lower()
        if original_name == new_name:
            continue

        # 处理注释
        comment = col['COLUMN_COMMENT']
        if comment:
            # 转义单引号为两个单引号
            escaped_comment = comment.replace("'", "''")
            comment_clause = f"COMMENT '{escaped_comment}'"
        else:
            comment_clause = ""

        # 构造列定义
        nullable = 'NOT NULL' if col['IS_NULLABLE'] == 'NO' else 'NULL'
        default_clause = f"DEFAULT {col['COLUMN_DEFAULT']}" if col['COLUMN_DEFAULT'] is not None else ''
        extra = col['EXTRA']

        # 拼接列定义（处理注释）
        column_def = ' '.join(filter(None, [
            col['COLUMN_TYPE'],
            nullable,
            default_clause,
            extra,
            comment_clause if comment else None
        ])).strip()

        alter_parts.append(f"CHANGE COLUMN `{original_name}` `{new_name}` {column_def}")

    return alter_parts


def main():
    # 数据库配置
    config = {
        'host': '123.56.171.51',
        'user': 'root',
        'password': 'lgmi6395',
        'database': 'jg_base_new'
    }

    # 提示用户备份
    print("警告：此脚本将直接修改数据库结构！")
    print("执行前请确保已备份数据库。")
    input("按 Enter 键继续，或 Ctrl+C 退出...")

    # 连接数据库
    connection = get_db_connection(config)
    if not connection:
        return

    try:
        tables = get_all_tables(connection)
        if not tables:
            print("数据库中没有表。")
            return

        for table in tables:
            print(f"\n处理表: {table}")
            column_details = get_column_details(connection, config['database'], table)
            if not column_details:
                continue

            alter_parts = generate_alter_statements(column_details)
            if alter_parts is None:
                print(f"  - 跳过：存在转小写后重复的列名")
                continue
            elif not alter_parts:
                print(f"  - 无需修改")
                continue

            alter_sql = f"ALTER TABLE `{table}` " + ", ".join(alter_parts)
            print(f"  - 执行SQL: {alter_sql}")

            try:
                with connection.cursor() as cursor:
                    cursor.execute(alter_sql)
                connection.commit()
                print(f"  - 成功")
            except Error as e:
                connection.rollback()
                print(f"  - 失败: {e}")

    finally:
        connection.close()


if __name__ == "__main__":
    main()